Date.prototype.Format = function (fmt) {
    var o = {
        'M+': this.getMonth() + 1, //月份
        'd+': this.getDate(), //日
        'h+': this.getHours(), //小时
        'm+': this.getMinutes(), //分
        's+': this.getSeconds(), //秒
        'q+': Math.floor((this.getMonth() + 3) / 3), //季度
        'S': this.getMilliseconds()//毫秒
    };
    if (/(y+)/.test(fmt)) fmt = fmt.replace(RegExp.$1, (this.getFullYear() + '').substr(4 - RegExp.$1.length));
    for (var k in o)
        if (new RegExp('(' + k + ')').test(fmt)) fmt = fmt.replace(RegExp.$1, (RegExp.$1.length == 1) ? (o[k]) : (('00' + o[k]).substr(('' + o[k]).length)));
    return fmt;
};

exports.sqlParser = function (sql,conditions) {
    //1 prepare duration
    var syntaxError = false;
    sql = sql.replace(/\n/g, ' ');
    sql = columnLowerCase(sql);

    //2 parse conditions  duration
    sql = parseCondition(conditions, sql);
    if (sql == '')
        syntaxError = true;

    //3 final check duration
    if (syntaxError) {
        sql = '';
    }
    //check if exist '{}' and '#'
    //case when then
    sql = sql.replace(/[A-z|0-9|_|.]+[\s]*[=|>|<]*[\s]*CASE[\s]+#[A-z|0-9|_|=|.]+[\s]+WHEN[\u4E00-\u9FA5|\s|.|A-z|0-9|'|_|-|:]+END/gi,' ');
    //LIKE #
    sql = sql.replace(/[\s]+[A-z|0-9|_|.]*[\s]*[=|>|<|LIKE]*[\s]*%?#[A-z|0-9|_|=|.]+%?/gi, ' ');
    //LIKE {}
    sql = sql.replace(/[\s]+[A-z|0-9|_|.]*[\s]*[=|>|<|LIKE]*[\s]*%?\{[A-z|0-9|_|=|.]+\s*}%?/gi, ' ');
    //{}
    sql = sql.replace(/\{\s*[A-z|0-9|_|.|\s]+\s*}/gi, '');

    //check if exist 'and and' or 'and' or 'where' in end and 'and group by/order by'
    sql = dealWithMultiAndOR(sql);
    sql = dealWithSqlEnd(sql);
    sql = dealWithSqlEndEX(sql);
    sql = sql.replace(/\s+where\s*$/i,'');
    sql = sql.replace(/\s+where\s+(group|order)\s+/i,' $1 ');
    sql = sql.replace(/\s*(and|or)\s*\)/gi,' )');
    sql = sql.replace(/\(\s*(and|or)\s*/gi,'( ');
    sql = sql.replace(/\(\s*(and|or)?\s*\)/gi,' ');
    sql = dealWithMultiAndOR(sql);

    if (sql.substring(sql.length - 5, sql.length) == 'where') {
        sql = sql.substring(0, sql.length - 5);
    }

    //deal where and
    sql = sql.replace(/\s+where\s+and\s+/gi, ' where ');
    sql = sql.replace(/\s+and\s+where\s+/gi, ' where ');

    //deal multi comma like , ,
    (function comma(){
        if(sql.match(/(,\s+,)/gi, ' , ')) {
            sql = sql.replace(/(,\s+,)/gi, ' , ')
            comma(sql);
        } else {
            return;
        }
    })();
    // deal , where | set , | ( , )
    sql = sql.replace(/,\s+where\s+/gi, ' where ');
    sql = sql.replace(/SET\s+,\s+/gi, ' set ');
    sql = sql.replace(/\(\s*,/gi, ' ( ');
    sql = sql.replace(/,\s*\)/gi, ' ) ');
    return sql;
};

function columnLowerCase(sql) {
    if(sql.match(/\s+as\s+([\S]+)/i)) {
        sql = sql.replace(/\s+as\s+([\S]+)/i,' '+RegExp.$1.toLowerCase());
        sql = columnLowerCase(sql);
    }
    return sql;
}

function dealWithSqlEnd(trimSql) {
   // \s+(?:and|or)\s+(group|order)\s+
    if (trimSql.match(/\s+(and|or)\s*$/i)) {
        trimSql = trimSql.replace(/\s+(and|or)\s*$/i, '');
        trimSql = dealWithSqlEnd(trimSql);
    }
    return trimSql;
}

function dealWithSqlEndEX(trimSql) {
    if (trimSql.match(/\s+(?:and|or)\s+(group|order)\s+/i)) {
        trimSql = trimSql.replace(/\s+(?:and|or)\s+(group|order)\s+/i, ' $1 ');
        trimSql = dealWithSqlEndEX(trimSql);
    }
    //console.log('\n-----------------------\n'+trimSql)
    return trimSql;
}

function dealWithMultiAndOR(trimSql) {
    if (trimSql.match(/\s+(and|or)\s+(and|or)\s+/i)) {
        trimSql = trimSql.replace(/\s+(and|or)\s+(and|or)\s+/i, ' $2 ');
        trimSql = dealWithMultiAndOR(trimSql);
    }
    return trimSql;

}

function parseCondition(conditions, sql) {
    if (typeof conditions == 'undefined') {
        console.log('all data');
    }
    sql = parseLike(conditions , sql);
    for (var condition in conditions) {
        var reg = new RegExp('#' + condition, 'i');
        var type = typeof conditions[condition];

        if (type === 'string') {
            if (conditions[condition].trim() == '') {
                //default all criteria, remove condition
                var regAll = new RegExp('([A-z|0-9|_|.]+)\s*=\s*#' + condition, 'i');
                sql = sql.replace(regAll, '');
            } else {
                sql = sql.replace(reg, '\'' + conditions[condition] + '\'');
            }
        } else if (type === 'number') {
            sql = sql.replace(reg, conditions[condition]);
        } else if (type === 'object') {
            if (conditions[condition] instanceof Array) {
                //multi value
                var reg2 = new RegExp('([A-z|0-9|_|.]+)\\s*=\\s*#' + condition, 'i');
                var reg3 = new RegExp('#' + condition, 'gi');

                //step1 : find and symbol it
                sql = sql.replace(reg2, '\$$$1\$');

                //step2 : construct sql from array
                var tempArray = conditions[condition];
                if (tempArray.length == 0) {
                    sql = sql.replace(/\$([A-z|0-9|_|.]+)\$/, '');
                } else {
                    var tempSql = '1 and (';
                    for (var i = 0; i < tempArray.length; i++) {
                        if (typeof tempArray[i] === 'string') {
                            tempSql += ('#' + condition + '=\'' + tempArray[i] + '\' or ');
                        } else if (typeof tempArray[i] === 'number') {
                            tempSql += ('#' + condition + '=' + tempArray[i] + ' or ');
                        }
                    }
                    var multiSql = tempSql.substring(0, tempSql.length - 4) + ')';

                    //step3 : replace array-sql #var
                    var toRep = sql.match(/\$([A-z|0-9|_|.]+)\$/);
                    if (toRep != null) {
                        multiSql = multiSql.replace(reg3, toRep[0]);
                        multiSql = multiSql.replace(/\$/g, '');
                        //step4 : replace whole sql $var$ -> var
                        sql = sql.replace(/\$([A-z|0-9|_|.]+)\$/, multiSql);
                    }
                }
                //console.log('after multi replace: '+ sql);
            } else {
                if(!conditions[condition]) {
                    continue;
                }
                var fromDate = null;
                var toDate = null;
                var replaceStr = '';
                if (conditions[condition]['from']) {
                    var tempFrom = new Date(conditions[condition]['from']);
                    fromDate = "'" + tempFrom.Format('yyyy-MM-dd hh:mm:ss') + "'";
                }
                if (conditions[condition]['to']) {
                    var tempTo = new Date(conditions[condition]['to']);
                    toDate = "'" + tempTo.Format('yyyy-MM-dd hh:mm:ss') + "'";
                }
                if (fromDate != null) {
                    replaceStr += condition + '>=' + fromDate + ' and ';
                }
                if (toDate != null) {
                    replaceStr += condition + '<=' + toDate + ' and ';
                }
                if (fromDate == null && toDate == null) {
                    sql = sql.replace(reg, '');
                } else {
                    replaceStr = replaceStr.substring(0, replaceStr.length - 5);
                    sql = sql.replace(reg, replaceStr);
                }
                //console.log('after date replace:'+ sql);
            }
        } else if (type === 'undefined') {
            return '';
        } else {
            console.log(type);
        }
    }
    return sql;
}

function parseLike(conditions , sql) {
    var reg = new RegExp('([A-z|0-9|_|.]+)\\s+LIKE\\s+(%?)#([A-z|0-9|.|_]+)(%?)','i');
    if(sql.match(reg)) {
        if(!!conditions[RegExp.$3]) {
            sql = sql.replace(reg,"$1 like '$2"+conditions[RegExp.$3]+"$4'");
        } else {
            sql = sql.replace(reg,' ');
        }
        return parseLike(conditions,sql);
    } else {
        return sql;
    }
}

function parseFilterLike(conditions , sql) {
    var reg = new RegExp('([A-z|0-9|_|.]+)\\s+LIKE\\s+(%?){\\s*([A-z|0-9|.|_]+)\\s*}(%?)','i');
    if(sql.match(reg)) {
        if(!!conditions[RegExp.$3]) {
            sql = sql.replace(reg,"$1 like '$2"+conditions[RegExp.$3]+"$4'");
        } else {
            sql = sql.replace(reg,' ');
        }
        return parseLike(conditions,sql);
    } else {
        return sql;
    }
}